import Database from 'better-sqlite3';
import path from 'path';
import fs from 'fs';
import bcrypt from 'bcryptjs';
import { fileURLToPath } from 'url';

const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);

const dataDir = path.join(__dirname, '..', '..', 'data');
if (!fs.existsSync(dataDir)) {
  fs.mkdirSync(dataDir, { recursive: true });
}
const dbPath = path.join(dataDir, 'app.db');

export const db = new Database(dbPath);

export function runMigrations() {
  const schema = `
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT UNIQUE NOT NULL,
    password_hash TEXT NOT NULL,
    role TEXT NOT NULL CHECK(role IN ('BIDDER','TENDERER','ADMIN')),
    company_name TEXT,
    legal_person TEXT,
    phone TEXT,
    created_at TEXT DEFAULT (datetime('now'))
  );

  CREATE TABLE IF NOT EXISTS tenders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    tender_code TEXT UNIQUE NOT NULL,
    drug_name TEXT NOT NULL,
    manufacturer TEXT NOT NULL,
    package_req TEXT,
    total_quantity INTEGER NOT NULL,
    deadline TEXT NOT NULL,
    deposit_amount REAL NOT NULL,
    created_by INTEGER NOT NULL,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now')),
    FOREIGN KEY (created_by) REFERENCES users(id)
  );

  CREATE TABLE IF NOT EXISTS bids (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    bid_date TEXT NOT NULL,
    bidder_id INTEGER NOT NULL,
    legal_person TEXT,
    phone TEXT,
    tender_id INTEGER NOT NULL,
    product_batch TEXT,
    production_date TEXT,
    expiry_date TEXT,
    total_amount REAL NOT NULL,
    deposit_paid_date TEXT,
    created_at TEXT DEFAULT (datetime('now')),
    FOREIGN KEY (bidder_id) REFERENCES users(id),
    FOREIGN KEY (tender_id) REFERENCES tenders(id)
  );

  CREATE TABLE IF NOT EXISTS messages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    author_id INTEGER NOT NULL,
    tender_id INTEGER,
    content TEXT NOT NULL,
    created_at TEXT DEFAULT (datetime('now')),
    FOREIGN KEY (author_id) REFERENCES users(id),
    FOREIGN KEY (tender_id) REFERENCES tenders(id)
  );
  `;

  db.exec(schema);

  const adminExists = db.prepare('SELECT 1 FROM users WHERE role = ? LIMIT 1').get('ADMIN');
  if (!adminExists) {
    const hash = bcrypt.hashSync('admin123', 10);
    db.prepare('INSERT INTO users (username, password_hash, role, company_name) VALUES (?,?,?,?)')
      .run('admin', hash, 'ADMIN', 'System Admin');
  }
}


